package database

import (
	"fmt"
	"strings"
)

// SQLQueryBuilder SQL查询构建器接口
type SQLQueryBuilder interface {
	// 基础查询方法
	Select(columns ...string) SQLQueryBuilder
	From(table string) SQLQueryBuilder
	Where(condition string, args ...interface{}) SQLQueryBuilder
	AndWhere(condition string, args ...interface{}) SQLQueryBuilder
	OrWhere(condition string, args ...interface{}) SQLQueryBuilder
	Join(joinType, table, condition string) SQLQueryBuilder
	GroupBy(columns ...string) SQLQueryBuilder
	Having(condition string, args ...interface{}) SQLQueryBuilder
	OrderBy(column string, direction string) SQLQueryBuilder
	Limit(limit int) SQLQueryBuilder
	Offset(offset int) SQLQueryBuilder

	// 生成SQL语句
	Build() (string, []interface{})
	BuildWithDialect(dialect SQLDialect) (string, []interface{})

	// 生成COUNT查询
	BuildCount() (string, []interface{})

	// 克隆查询构建器
	Clone() SQLQueryBuilder
}

// BaseSQLQueryBuilder 基础SQL查询构建器实现
type BaseSQLQueryBuilder struct {
	columns     []string
	table       string
	alias       string
	joins       []string
	whereClause []string
	whereArgs   []interface{}
	groupBy     []string
	having      string
	havingArgs  []interface{}
	orderBy     []string
	limitVal    *int
	offsetVal   *int
}

// NewSQLQueryBuilder 创建查询构建器实例
func NewSQLQueryBuilder() SQLQueryBuilder {
	return &BaseSQLQueryBuilder{
		columns:     []string{},
		joins:       []string{},
		whereClause: []string{},
		whereArgs:   []interface{}{},
		groupBy:     []string{},
		orderBy:     []string{},
	}
}

// Select 选择列
func (b *BaseSQLQueryBuilder) Select(columns ...string) SQLQueryBuilder {
	b.columns = append(b.columns, columns...)
	return b
}

// From 设置表名
func (b *BaseSQLQueryBuilder) From(table string) SQLQueryBuilder {
	parts := strings.SplitN(table, " ", 2)
	b.table = parts[0]
	if len(parts) > 1 {
		b.alias = parts[1]
	}
	return b
}

// Where 添加WHERE条件
func (b *BaseSQLQueryBuilder) Where(condition string, args ...interface{}) SQLQueryBuilder {
	b.whereClause = []string{condition}
	b.whereArgs = args
	return b
}

// AndWhere 添加AND条件
func (b *BaseSQLQueryBuilder) AndWhere(condition string, args ...interface{}) SQLQueryBuilder {
	if len(b.whereClause) > 0 {
		b.whereClause = append(b.whereClause, condition)
		b.whereArgs = append(b.whereArgs, args...)
	} else {
		b.Where(condition, args...)
	}
	return b
}

// OrWhere 添加OR条件
func (b *BaseSQLQueryBuilder) OrWhere(condition string, args ...interface{}) SQLQueryBuilder {
	if len(b.whereClause) > 0 {
		b.whereClause = append(b.whereClause, "OR "+condition)
		b.whereArgs = append(b.whereArgs, args...)
	} else {
		b.Where(condition, args...)
	}
	return b
}

// Join 添加JOIN
func (b *BaseSQLQueryBuilder) Join(joinType, table, condition string) SQLQueryBuilder {
	join := fmt.Sprintf("%s JOIN %s ON %s", joinType, table, condition)
	b.joins = append(b.joins, join)
	return b
}

// GroupBy 添加GROUP BY
func (b *BaseSQLQueryBuilder) GroupBy(columns ...string) SQLQueryBuilder {
	b.groupBy = append(b.groupBy, columns...)
	return b
}

// Having 添加HAVING条件
func (b *BaseSQLQueryBuilder) Having(condition string, args ...interface{}) SQLQueryBuilder {
	b.having = condition
	b.havingArgs = args
	return b
}

// OrderBy 添加ORDER BY
func (b *BaseSQLQueryBuilder) OrderBy(column string, direction string) SQLQueryBuilder {
	if direction != "ASC" && direction != "DESC" {
		direction = "ASC"
	}
	b.orderBy = append(b.orderBy, column+" "+direction)
	return b
}

// Limit 设置LIMIT
func (b *BaseSQLQueryBuilder) Limit(limit int) SQLQueryBuilder {
	b.limitVal = &limit
	return b
}

// Offset 设置OFFSET
func (b *BaseSQLQueryBuilder) Offset(offset int) SQLQueryBuilder {
	b.offsetVal = &offset
	return b
}

// Build 构建SQL查询
func (b *BaseSQLQueryBuilder) Build() (string, []interface{}) {
	return b.BuildWithDialect(nil)
}

// BuildWithDialect 使用特定方言构建SQL查询
func (b *BaseSQLQueryBuilder) BuildWithDialect(dialect SQLDialect) (string, []interface{}) {
	// 构建SELECT子句
	columns := "*"
	if len(b.columns) > 0 {
		columns = strings.Join(b.columns, ", ")
	}

	// 构建FROM子句
	from := b.table
	if b.alias != "" {
		from += " " + b.alias
	}

	// 构建JOIN子句
	joins := ""
	if len(b.joins) > 0 {
		joins = " " + strings.Join(b.joins, " ")
	}

	// 构建WHERE子句
	where := ""
	if len(b.whereClause) > 0 {
		where = " WHERE " + strings.Join(b.whereClause, " AND ")
	}

	// 构建GROUP BY子句
	groupBy := ""
	if len(b.groupBy) > 0 {
		groupBy = " GROUP BY " + strings.Join(b.groupBy, ", ")
	}

	// 构建HAVING子句
	having := ""
	if b.having != "" {
		having = " HAVING " + b.having
	}

	// 构建ORDER BY子句
	orderBy := ""
	if len(b.orderBy) > 0 {
		orderBy = " ORDER BY " + strings.Join(b.orderBy, ", ")
	}

	// 构建LIMIT和OFFSET子句
	limitOffset := ""
	if b.limitVal != nil {
		if dialect != nil {
			var offset int
			if b.offsetVal != nil {
				offset = *b.offsetVal
			} else {
				offset = 0
			}
			limitOffset = " " + dialect.GetLimitClause(*b.limitVal, offset)
		} else {
			limitOffset = fmt.Sprintf(" LIMIT %d", *b.limitVal)
			if b.offsetVal != nil {
				limitOffset += fmt.Sprintf(" OFFSET %d", *b.offsetVal)
			}
		}
	} else if b.offsetVal != nil {
		if dialect != nil {
			limitOffset = " " + dialect.GetLimitClause(-1, *b.offsetVal)
		} else {
			limitOffset = fmt.Sprintf(" OFFSET %d", *b.offsetVal)
		}
	}

	// 组合完整的SQL语句
	sql := fmt.Sprintf("SELECT %s FROM %s%s%s%s%s%s%s",
		columns, from, joins, where, groupBy, having, orderBy, limitOffset)

	// 组合参数
	args := make([]interface{}, 0, len(b.whereArgs)+len(b.havingArgs))
	args = append(args, b.whereArgs...)
	args = append(args, b.havingArgs...)

	return sql, args
}

// BuildCount 构建COUNT查询
func (b *BaseSQLQueryBuilder) BuildCount() (string, []interface{}) {
	// 克隆查询构建器并清除可能影响COUNT的部分
	clone := b.Clone().(*BaseSQLQueryBuilder)
	clone.columns = []string{"COUNT(*) AS count"}
	clone.orderBy = []string{}
	clone.limitVal = nil
	clone.offsetVal = nil

	return clone.Build()
}

// Clone 克隆查询构建器
func (b *BaseSQLQueryBuilder) Clone() SQLQueryBuilder {
	clone := &BaseSQLQueryBuilder{
		table:       b.table,
		alias:       b.alias,
		columns:     make([]string, len(b.columns)),
		joins:       make([]string, len(b.joins)),
		whereClause: make([]string, len(b.whereClause)),
		whereArgs:   make([]interface{}, len(b.whereArgs)),
		groupBy:     make([]string, len(b.groupBy)),
		having:      b.having,
		havingArgs:  make([]interface{}, len(b.havingArgs)),
		orderBy:     make([]string, len(b.orderBy)),
	}

	copy(clone.columns, b.columns)
	copy(clone.joins, b.joins)
	copy(clone.whereClause, b.whereClause)
	copy(clone.whereArgs, b.whereArgs)
	copy(clone.groupBy, b.groupBy)
	copy(clone.havingArgs, b.havingArgs)
	copy(clone.orderBy, b.orderBy)

	if b.limitVal != nil {
		val := *b.limitVal
		clone.limitVal = &val
	}

	if b.offsetVal != nil {
		val := *b.offsetVal
		clone.offsetVal = &val
	}

	return clone
}

// InsertBuilder 插入SQL构建器
type InsertBuilder struct {
	table     string
	columns   []string
	values    [][]interface{}
	returning string
}

// NewInsertBuilder 创建插入构建器
func NewInsertBuilder(table string) *InsertBuilder {
	return &InsertBuilder{
		table:   table,
		columns: []string{},
		values:  [][]interface{}{},
	}
}

// Columns 设置列名
func (b *InsertBuilder) Columns(columns ...string) *InsertBuilder {
	b.columns = columns
	return b
}

// Values 添加一行值
func (b *InsertBuilder) Values(values ...interface{}) *InsertBuilder {
	if len(values) != len(b.columns) && len(b.columns) > 0 {
		panic("values count must match columns count")
	}
	b.values = append(b.values, values)
	return b
}

// Returning 设置RETURNING子句（PostgreSQL）
func (b *InsertBuilder) Returning(columns string) *InsertBuilder {
	b.returning = columns
	return b
}

// Build 构建INSERT语句
func (b *InsertBuilder) Build() (string, []interface{}) {
	if len(b.columns) == 0 {
		panic("no columns specified for insert")
	}

	// 构建列部分
	columns := strings.Join(b.columns, ", ")

	// 构建VALUES部分和收集参数
	var placeholders []string
	var args []interface{}

	for _, row := range b.values {
		rowPlaceholders := make([]string, len(row))
		for i := range row {
			rowPlaceholders[i] = "?"
			args = append(args, row[i])
		}
		placeholders = append(placeholders, "("+strings.Join(rowPlaceholders, ", ")+")")
	}

	// 构建完整SQL
	sql := fmt.Sprintf("INSERT INTO %s (%s) VALUES %s",
		b.table, columns, strings.Join(placeholders, ", "))

	// 添加RETURNING子句（如果有）
	if b.returning != "" {
		sql += " RETURNING " + b.returning
	}

	return sql, args
}

// UpdateBuilder 更新SQL构建器
type UpdateBuilder struct {
	table       string
	sets        map[string]interface{}
	whereClause []string
	whereArgs   []interface{}
	returning   string
}

// NewUpdateBuilder 创建更新构建器
func NewUpdateBuilder(table string) *UpdateBuilder {
	return &UpdateBuilder{
		table:       table,
		sets:        make(map[string]interface{}),
		whereClause: []string{},
		whereArgs:   []interface{}{},
	}
}

// Set 设置更新字段
func (b *UpdateBuilder) Set(column string, value interface{}) *UpdateBuilder {
	b.sets[column] = value
	return b
}

// SetMap 从映射设置多个字段
func (b *UpdateBuilder) SetMap(m map[string]interface{}) *UpdateBuilder {
	for k, v := range m {
		b.sets[k] = v
	}
	return b
}

// Where 添加WHERE条件
func (b *UpdateBuilder) Where(condition string, args ...interface{}) *UpdateBuilder {
	b.whereClause = []string{condition}
	b.whereArgs = args
	return b
}

// AndWhere 添加AND条件
func (b *UpdateBuilder) AndWhere(condition string, args ...interface{}) *UpdateBuilder {
	if len(b.whereClause) > 0 {
		b.whereClause = append(b.whereClause, condition)
		b.whereArgs = append(b.whereArgs, args...)
	} else {
		b.Where(condition, args...)
	}
	return b
}

// OrWhere 添加OR条件
func (b *UpdateBuilder) OrWhere(condition string, args ...interface{}) *UpdateBuilder {
	if len(b.whereClause) > 0 {
		b.whereClause = append(b.whereClause, "OR "+condition)
		b.whereArgs = append(b.whereArgs, args...)
	} else {
		b.Where(condition, args...)
	}
	return b
}

// Returning 设置RETURNING子句（PostgreSQL）
func (b *UpdateBuilder) Returning(columns string) *UpdateBuilder {
	b.returning = columns
	return b
}

// Build 构建UPDATE语句
func (b *UpdateBuilder) Build() (string, []interface{}) {
	if len(b.sets) == 0 {
		panic("no columns specified for update")
	}

	// 构建SET部分
	setParts := make([]string, 0, len(b.sets))
	args := make([]interface{}, 0, len(b.sets)+len(b.whereArgs))

	for col, val := range b.sets {
		setParts = append(setParts, col+" = ?")
		args = append(args, val)
	}

	// 构建WHERE部分
	where := ""
	if len(b.whereClause) > 0 {
		where = " WHERE " + strings.Join(b.whereClause, " AND ")
		args = append(args, b.whereArgs...)
	}

	// 构建完整SQL
	sql := fmt.Sprintf("UPDATE %s SET %s%s",
		b.table, strings.Join(setParts, ", "), where)

	// 添加RETURNING子句（如果有）
	if b.returning != "" {
		sql += " RETURNING " + b.returning
	}

	return sql, args
}
